package com.rwy.dao;

import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Objects;

import com.rwy.dto.device.HandleFaultRecordDTO;
import com.rwy.dto.device.HandleFaultTaskDTO;
import com.rwy.util.CollectionUtil;
import com.rwy.util.StringUtil;

import lombok.extern.log4j.Log4j;

/** handle fault provider */
@Log4j
public class HandleFaultProvider {

	/** create wait handle task 可能存在 修改终端状态正确,但是没有上传处理数据, 导致数据第二次不能创建 */
	public String createFaultTask(Map<String, Object> param) {
		StringBuffer sb = new StringBuffer();
		sb.append(" INSERT INTO device_fault_handing (  ");
		sb.append("   terminalID, salesNetworkID, handStatus,createTime, printerStatusID");
		sb.append("  ) ");
		sb.append(" SELECT distinct a.TerminalID,   ");
		sb.append(" a.SalesNetworkID,   ");
		sb.append(" 301, getdate(), printerStatusID");
		sb.append(" FROM Device_Terminal a  ");
		sb.append(" INNER JOIN CRM_SalesNetwork b ");
		sb.append(" ON b.SalesNetworkID = a.SalesNetworkID ");
		sb.append(" INNER JOIN Admin_EmployeeManageSalesNetwork  d ");
		sb.append(" ON d.SalesNetworkID = b.SalesNetworkID ");
		//sb.append(" INNER JOIN Admin_Employee e ");
	//	sb.append(" ON  d.EmployeeID = e.EmployeeID ");
		sb.append(" WHERE  printerStatusID IN (201,202,203,301,302,303,304) ");
		//sb.append(" AND e.IsEnabled =1 ");
	//	sb.append(" AND NOT EXISTS (SELECT 1 FROM device_fault_handing WHERE terminalID = a.TerminalID AND handStatus IN(301,303)) ");
		sb.append("  AND NOT EXISTS ( ");
		sb.append("       SELECT 1 FROM device_fault_handing   ");
		sb.append("       WHERE terminalID = a.TerminalID    ");
		sb.append("		 AND (handStatus IN(301,303) OR (handStatus = 302 AND updateTime >= dateadd(MINUTE,-5,  getdate())))  ");
		sb.append(" ) ");
			return sb.toString();
	}
	
	
	public String  updateDisconnectTeriminal() {
		StringBuffer sb  = new StringBuffer();
		sb.append(" update a");
		sb.append(" set a.terminalStatus = 601 ");
		sb.append(" from device_fault_handing a");
		sb.append(" , ( ");
		sb.append("  select  DISTINCT a.TerminalID,b.SalesNetworkID  ");
		sb.append("  from  Device_Terminal a   ");
		sb.append("  INNER JOIN Admin_EmployeeManageSalesNetwork b  ");
		sb.append("  ON a.SalesNetworkID = b.SalesNetworkID   ");
		sb.append("  where  a.IsEnabled = 1   ");
		sb.append("  AND a.LastActiveTime < dateadd(MINUTE,-5,GETDATE())   ");
		sb.append("  AND  EXISTS (SELECT 1 FROM device_fault_handing WHERE terminalID = a.TerminalID  AND SalesNetworkID = b.SalesNetworkID AND terminalStatus  IS NULL AND handStatus != 302) ");
		sb.append("  )b ");
		sb.append(" where a.terminalID = b.terminalID ");
		sb.append(" and a.SalesNetworkID = b.SalesNetworkID ");
		sb.append(" and a.handStatus != 302  ");
		//log.info(sb.toString());
		return sb.toString();
	}
	
	public String  createDisconnectTeriminal() {
		StringBuffer sb  = new StringBuffer();
		sb.append(" INSERT INTO device_fault_handing (  ");
		sb.append("   terminalID, salesNetworkID, handStatus,createTime, terminalStatus");
		sb.append("  ) ");
		sb.append("  select  DISTINCT a.TerminalID,b.SalesNetworkID, 301, getdate(),601 ");
		sb.append(" from  Device_Terminal a  ");
		sb.append(" INNER JOIN Admin_EmployeeManageSalesNetwork b   ");
		sb.append(" ON a.SalesNetworkID = b.SalesNetworkID   ");
		sb.append(" where   a.IsEnabled = 1 ");
		sb.append(" AND a.LastActiveTime < dateadd(MINUTE,-5,GETDATE()) ");
	    // 修改添加排除最近五分钟标示已处理的终端
		// 	sb.append(" AND  NOT EXISTS (SELECT 1 FROM device_fault_handing WHERE terminalID = a.TerminalID  AND SalesNetworkID = b.SalesNetworkID  AND handStatus != 302) ");
		sb.append("  AND  NOT EXISTS (   ");
		sb.append("   SELECT 1 FROM device_fault_handing   ");
		sb.append("    WHERE terminalID = a.TerminalID   ");
		sb.append("    AND SalesNetworkID = b.SalesNetworkID  ");
		sb.append("   AND (handStatus != 302 OR (handStatus = 302 AND updateTime >= dateadd(MINUTE, -5 , GETDATE()) ))   ");
		sb.append(" ) ");
		//log.info(sb.toString());
		return sb.toString();
	}
	
	/** create wait advice employee task */
	public String createAdviceTask() {
		StringBuffer sb = new StringBuffer();
		sb.append(" INSERT INTO device_fault_handing_advice (  ");
		sb.append("    handID, employeeID, employeeName,adviceStatus, createTime");
		sb.append("  ) ");
		sb.append(" SELECT   ");
		sb.append("   distinct  a.handID, c.EmployeeID, c.EmployeeName,501,getdate() ");
		sb.append(" FROM device_fault_handing a  ");
		sb.append("   INNER JOIN Admin_EmployeeManageSalesNetwork b   ");
		sb.append("   ON a.salesNetworkID = b.SalesNetworkID  ");
		sb.append("   INNER JOIN  Admin_Employee c   ");
		sb.append("   ON c.EmployeeID = b.EmployeeID ");
		sb.append("    WHERE a.handStatus = 301   ");
		sb.append("    AND c.IsEnabled =1 ");
		sb.append("    AND NOT EXISTS (SELECT 1 FROM device_fault_handing_advice WHERE handID = a.handID AND employeeID = c.EmployeeID) ");
		return sb.toString();
	}
	
	/** advice employee*/
	public String adviceFaultTaskForEmployee(Map<String, Object> param) {
		StringBuffer sb = new StringBuffer();
		
		sb.append(" SELECT   ");
		sb.append("     b.adviceID, a.EmployeeID as adviceEmployID, a.EmployeeName as adviceEmployName, isnull(a.mobile,'0000') as mobile  ");
		sb.append("   FROM Admin_Employee a  ");
		sb.append("   INNER JOIN device_fault_handing_advice b ");
		sb.append("    ON a.EmployeeID = b.employeeID ");
		sb.append("   WHERE b.adviceStatus = 501  ");
		sb.append("   AND a.IsEnabled = 1 ");
		return sb.toString();
	}
	
	/** update device_fault_handing */
	public String updateFaultHandling(Map<String, Object> map) {
		StringBuffer sb = new StringBuffer();
		HandleFaultTaskDTO param = (HandleFaultTaskDTO) map.get("task");
		Integer handID = param.getHandID();
	//	String adviceMes =  param.getAdviceMess();
		//Integer adviceStatus = param.getAdviceStatus();
		Integer handStatus = param.getHandStatus();
		Integer updateUser = param.getUpdateUser();
		List<Integer> ids = param.getIds();
		
		sb.append(" update device_fault_handing ");
		sb.append("   set  ");
		
		if (!Objects.isNull(handStatus)) {
			sb.append(" handStatus = "+ handStatus + ", ");
		}
		if (!Objects.isNull(updateUser)) {
			sb.append(" updateUser = "+ updateUser +", ");
		}
		sb.append(" updateTime = getdate()  ");
		sb.append(" where 1=1 ");
		if(!CollectionUtil.isEmpty(ids)) {
			sb.append(" and handID in (");
			for(int i=0 ;i<ids.size(); i++) {
				sb.append(ids.get(i)+"");
				if(i != (ids.size()-1)) {
					sb.append(",");
				}
			}
			sb.append(")");
		}
		if (handID != null) {
			sb.append("and handID =" + handID);
		}
		return sb.toString();
	}
	
	
	
	/** update device_fault_handing_advice */
	public String updateHandlAdvice(Map<String, Object> map) {
		StringBuffer sb = new StringBuffer();
		HandleFaultTaskDTO param = (HandleFaultTaskDTO) map.get("task");
		Integer adviceID = param.getAdviceID();
		String adviceMes =  param.getAdviceMess();
		Integer adviceStatus = param.getAdviceStatus();
		Integer updateUser = param.getUpdateUser();
		String remark = param.getRemark();
		List<Integer> ids = param.getIds();
		
		sb.append(" update device_fault_handing_advice ");
		sb.append("   set  ");
		if (!StringUtil.isEmpty(adviceMes)) {
			sb.append(" adviceMess = '"+ adviceMes +"', ");
		}
		if (!Objects.isNull(adviceStatus)) {
			sb.append(" adviceStatus = "+ adviceStatus+ ", ");
		}
		if (!Objects.isNull(updateUser)) {
			sb.append(" updateUser = "+ updateUser +", ");
		}
		if (!StringUtil.isEmpty(remark)) {
			sb.append(" remark = '" + remark+"', ");
		}
		sb.append(" updateTime = getdate()  ");
		sb.append(" where 1=1 ");
		if(!CollectionUtil.isEmpty(ids)) {
			sb.append(" and adviceID in (");
			for(int i=0 ;i<ids.size(); i++) {
				sb.append(ids.get(i)+"");
				if(i != (ids.size()-1)) {
					sb.append(",");
				}
			}
			sb.append(")");
		}
		if (adviceID != null) {
			sb.append("and adviceID =" + adviceID);
		}
		return sb.toString();
	}
	
	
	/* insert record */
	public String insertFaultHandlingRecord(Map<String, Object> map) {
		StringBuffer sb = new StringBuffer();
		HandleFaultRecordDTO record  = (HandleFaultRecordDTO) map.get("record");
		Integer handID = record.getHandID();
		Integer networkType = record.getNetworkType();
		Integer signalLevel = record.getSignalLevel();
		String faultCategoryIDs = record.getFaultCategoryIDs();
		String maintainRecord = record.getMaintianRecord();
		String printerPhotoUrl = record.getPrinterPhoto();
		String devicePhotoUrl = record.getDevicePhoto();
		String testPhotoUrl = record.getTestPhoto();
		String  num =  record.getNum();
		String employName =  record.getEmployName();
		Integer employID = record.getEmployID();
		Integer recordStatus = record.getRecordStatus();
		String wifiNo =  StringUtil.isEmpty(record.getWifiNo()) ? "": record.getWifiNo();
		
		sb.append(" insert into device_fault_handing_record (   ");
		sb.append("   handID, networkType, signalLevel, faultDescIDs, maintainRecord, ");
		sb.append("   printerPhoto, devicePhoto, testPhoto, num, employID,  ");
		sb.append("   employName, recordStatus, createTime, wifiNo) VALUES  ( ");
		sb.append(handID +", " + networkType + ", "+ signalLevel + ", '" + faultCategoryIDs + "', '");
		sb.append(maintainRecord +"', '" + printerPhotoUrl + "', '"+ devicePhotoUrl + "', '");
		sb.append(testPhotoUrl + "','" + num + "', " + employID + ",'");
		sb.append(employName + "', " + recordStatus+ ", getdate(), '" + wifiNo +"'");
		sb.append(")");
		//log.info(sb.toString());
		return sb.toString();
	}
	
	public String getFaultTaskItemCount(Map<String, Object> param) {
		StringBuffer sb = new StringBuffer();
		Integer handStatus = (Integer) param.get("handStatus");
		Integer employeeID = (Integer) param.get("employeeID");
		
		sb.append(" SELECT  ");
		sb.append("     count(*) ");
		sb.append(" FROM device_fault_handing a ");
		sb.append("   INNER JOIN Admin_EmployeeManageSalesNetwork ma ");
		sb.append("   ON a.salesNetworkID = ma.SalesNetworkID");
		sb.append("   INNER JOIN Admin_Employee ely ");
		sb.append("   ON ma.EmployeeID = ely.EmployeeID  ");
		sb.append("   INNER JOIN CRM_SalesNetwork b ");
		sb.append("   ON ma.SalesNetworkID = b.SalesNetworkID ");
		sb.append("   INNER JOIN Device_Terminal d   ");
		sb.append("   ON a.terminalID = d.TerminalID  ");
		sb.append(" WHERE a.handStatus = " + handStatus);
		sb.append(" AND ely.EmployeeID = " + employeeID);
		return sb.toString();
	}
	
	/** get fault task item */
	public String getFaultTaskItem(Map<String, Object> param) {
		StringBuffer sb = new StringBuffer();
		Integer handStatus = (Integer) param.get("handStatus");
		Integer employeeID = (Integer) param.get("employeeID");
		Integer pageSize = (Integer) param.get("pageSize");
		Integer page = (Integer) param.get("page");
		
		sb.append("select top "+ pageSize);
		sb.append(" * FROM (");
		sb.append("  select ");
		sb.append("    ROW_NUMBER() OVER (ORDER BY handID desc) AS RN, * ");
		sb.append("    FROM ( ");
		
		sb.append(" SELECT   ");
		sb.append("  	a.handID, 	b.SalesNetworkID, ");
		sb.append("     b.SalesNetworkName,  b.DetailedAddress as detailAddress, a.handStatus,  ");
		sb.append("     a.createTime, d.TerminalID, d.TerminalCode, ");
		sb.append("     b.Latitude, b.Longitude,      ");
		sb.append("     (  ");
		sb.append("       SELECT printerStatusName   ");
		sb.append("       FROM Device_PrinterStatus ");
		sb.append("       WHERE PrinterStatusID =  a.PrinterStatusID ");
		sb.append("     ) printerStatusDesc,  ");
		sb.append("     f.printerModel,         ");
		sb.append("     a.terminalStatus , a.updateUser ");
		sb.append("     FROM device_fault_handing a ");
	
		sb.append("     INNER JOIN Admin_EmployeeManageSalesNetwork ma ");
		sb.append("     ON a.salesNetworkID = ma.SalesNetworkID   ");
		sb.append("      INNER JOIN Admin_Employee ely  ");
		sb.append("     ON ma.EmployeeID = ely.EmployeeID ");
		sb.append("     INNER JOIN CRM_SalesNetwork b    ");
		sb.append("     ON ma.SalesNetworkID = b.SalesNetworkID ");
		sb.append("     INNER JOIN Device_Terminal d ");
		sb.append("     ON a.terminalID = d.TerminalID ");
		sb.append("     INNER JOIN (  ");
		sb.append("         SELECT  PrinterID,  ");
		sb.append("         (SELECT printerModelName FROM Device_PrinterModel WHERE PrinterModelID = a.PrinterModelID)  AS printerModel                        ");
		sb.append("         FROM Device_Printer a  ");
		sb.append("     ) f              ");
		sb.append("     ON d.printerID = f.PrinterID ");
		sb.append("     WHERE a.handStatus = " + handStatus);
		sb.append("     AND ely.EmployeeID = " + employeeID);
		sb.append("     and ely.isEnabled =1 ");
		sb.append(" )t  )t WHERE RN > "+((page-1) * pageSize));
		
		return sb.toString();
	}
	
	/** get maintain record */
	public String getHandleReocrdDetail(Map<String, Object> param) {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT c.*  ");
		sb.append("  FROM device_fault_handing_advice a ");
		sb.append("  INNER JOIN device_fault_handing b ");
		sb.append("  ON a.handID = b.handID ");
		return sb.toString();
	}
	
	public String getEmployeeByMobile(Map<String, Object> param) {
		StringBuffer sb = new StringBuffer();
		String mobile = (String) param.get("mobile");
		sb.append("  select  ");
		sb.append("    top 1  employeeID, employeeName, mobile  ");
		sb.append("  from Admin_Employee   ");
		sb.append("  where mobile = '" + mobile + "' and isEnabled =1  ");
		return sb.toString();
	}
	
	public String getDeviceMessage(Map<String, Object> param) {
		StringBuffer sb = new StringBuffer();
		Integer terminalID = (Integer) param.get("terminalID");
		Integer handID = (Integer) param.get("handID");
		sb.append(" SELECT  ");
		sb.append("  	a.handID, 	b.SalesNetworkID, a.terminalStatus, a.updateUser, ");
		sb.append("     b.SalesNetworkName,  b.DetailedAddress as detailAddress, a.handStatus,  ");
		sb.append("     a.createTime, d.TerminalID, d.TerminalCode, ");
		sb.append("     b.Latitude, b.Longitude,   a.terminalStatus,   ");
		sb.append("     (  ");
		sb.append("       SELECT printerStatusName   ");
		sb.append("       FROM Device_PrinterStatus ");
		sb.append("       WHERE PrinterStatusID =  a.printerStatusID ");
		sb.append("     ) printerStatusDesc,  ");
		sb.append("     f.printerModel        ");
		sb.append(" FROM device_fault_handing a ");
		sb.append(" INNER JOIN CRM_SalesNetwork b ");
		sb.append(" ON a.salesNetworkID = b.SalesNetworkID ");
		sb.append(" INNER JOIN Device_Terminal d ");
		sb.append(" ON a.terminalID = d.TerminalID ");
		sb.append(" INNER JOIN (  ");
		sb.append("         SELECT  PrinterID,  ");
		sb.append("         (SELECT printerModelName FROM Device_PrinterModel WHERE PrinterModelID = a.PrinterModelID)  AS printerModel                        ");
		sb.append("         FROM Device_Printer a  ");
		sb.append(" ) f              ");
		sb.append(" ON d.printerID = f.PrinterID ");		
		sb.append(" WHERE a.terminalID = " + terminalID);
		sb.append("  and a.handID = " + handID);
		return sb.toString();
	}
	
	/** 同步,两个状态都正常的数据 , but 任务状态未更新*/
	public String syncNormalTerminalStatus() {
		StringBuffer sb = new StringBuffer();
		
		sb.append("  UPDATE a  ");
		sb.append("    SET a.handStatus = 302, ");
		sb.append("        a.updateTime = getdate(), ");
		sb.append("        a.remark = '终端状态已正常, 系统自动修改维修状态为已处理' ,");
		sb.append("      a.updateUser = 1  ");
		sb.append("   FROM device_fault_handing a   ");
		sb.append("   ,(  ");
		sb.append("   	SELECT  DISTINCT terminalID, SalesNetworkID FROM Device_Terminal a WHERE PrinterStatusID IN (101,102) ");
		sb.append("   AND a.IsEnabled = 1  ");
		sb.append("   AND a.LastActiveTime >= dateadd(MINUTE,-5,GETDATE())  ");
		sb.append("   AND EXISTS (SELECT 1 FROM device_fault_handing WHERE handStatus IN (301,303) AND terminalID = a.terminalID AND SalesNetworkID = a.salesNetworkID AND printerStatusID != null) ");
		sb.append(" )b  ");
		sb.append(" WHERE a.SalesNetworkID = b.salesNetworkID  ");
		sb.append(" AND a.TerminalID = b.terminalID   ");
		sb.append(" AND  a.handStatus IN (301,303) ");
		return sb.toString();
		
	}
	

	
}
